import pandas as pd
import numpy as np
import os

month = '202411'
# pre_year = '202312'
# pre_month = '202410'
pre_year_int = int(int(month[0:4]) - 1)
pre_year = str(pre_year_int) + '12'
if month[4:6] != '01':
    pre_month = str(int(int(month) - 1))
else:
    pre_month = str(pre_year_int) + '12'
month_int = int(month[4:6])

company_name_list = ['中国宝武钢铁集团有限公司', '鞍钢集团有限公司', '江苏沙钢集团有限公司', '河钢集团有限公司', '北京建龙重工集团有限公司',
                     '首钢集团有限公司', '湖南钢铁集团有限公司', '德龙钢铁集团', '山东钢铁集团有限公司', '江西方大钢铁集团有限公司']
xlsx_name = 'D:/repos/sicost/' + month + '/吨钢利润,吨钢期间费用.xlsx'
df_1 = pd.read_excel(xlsx_name)
df_1 = df_1[['COMPANY_NAME', 'CURR_YEAR_VALUE_1']]
df_0 = pd.DataFrame(company_name_list, columns=['COMPANY_NAME'])
v = ['COMPANY_NAME']
df_0 = pd.merge(df_0, df_1, on=v, how='left')
df_0.rename(columns={'CURR_YEAR_VALUE_1': '吨钢利润'}, inplace=True)

# 首钢集团（还原矿石利润）

xlsx_name = 'D:/repos/sicost/' + month + '/实现利税,利润总额.xlsx'
df_2 = pd.read_excel(xlsx_name)
df_20 = df_2[df_2['COMPANY_NAME'] == '首钢集团有限公司']
df_20 = df_20.reset_index(drop=True)
profit = df_20.loc[0]['CURR_YEAR_VALUE_2']
# 利润
profit = profit / 10000
xlsx_name = 'D:/repos/sicost/' + month + '/NEW_吨钢EBITDA.xlsx'
df_3 = pd.read_excel(xlsx_name)
df_30 = df_3[df_3['COMPANY_NAME'] == '首钢集团有限公司']
df_30 = df_30.reset_index(drop=True)
# 钢产量
production = df_30.loc[0]['钢产量']
# 吨钢利润
profit_unit = profit / production * 10000
# 矿石利润
if month_int in [1, 2, 3]:
    ore_profit = 8 * month_int
elif month_int in [4, 5, 6]:
    ore_profit = 8 * 3 + 4 * (month_int - 3)
elif month_int in [7, 8, 9]:
    ore_profit = 8 * 3 + 4 * 3 + 4 * (month_int - 6)
elif month_int in [10, 11, 12]:
    ore_profit = 8 * 3 + 4 * 3 + 4 * 3 + 4 * (month_int - 9)
# 不含矿石利润
no_ore_profit = profit - ore_profit
# 吨钢利润（不含矿石）
new_profit_unit = no_ore_profit / production * 10000
df_out = pd.DataFrame(columns=['月份', '利润', '钢产量', '吨钢利润', '矿石利润', '不含矿石利润', '吨钢利润（不含矿石）'])
dict_out = {}
dict_out['月份'] = month
dict_out['利润'] = profit
dict_out['钢产量'] = production
dict_out['吨钢利润'] = profit_unit
dict_out['矿石利润'] = ore_profit
dict_out['不含矿石利润'] = no_ore_profit
dict_out['吨钢利润（不含矿石）'] = new_profit_unit
new_row = pd.Series(dict_out)
df_out = df_out.append(new_row, ignore_index=True)

writer = pd.ExcelWriter('D:/repos/sicost/' + month + '/NEW_首钢集团.xlsx')
df_out.to_excel(writer, sheet_name='Sheet1', index=False)
writer.save()
###################################################################
# 湖南钢铁（还原FMG投资收益及VAMA利润）
# xlsx_name = 'D:/repos/sicost/' + month + '/实现利税,利润总额.xlsx'
# df_2 = pd.read_excel(xlsx_name)
df_21 = df_2[df_2['COMPANY_NAME'] == '湖南钢铁集团有限公司']
df_21 = df_21.reset_index(drop=True)
df_22 = df_2[df_2['COMPANY_NAME'] == '其中：华菱湘潭钢铁有限公司']
df_22 = df_22.reset_index(drop=True)
df_23 = df_2[df_2['COMPANY_NAME'] == '      华菱涟源钢铁有限公司']
df_23 = df_23.reset_index(drop=True)
df_24 = df_2[df_2['COMPANY_NAME'] == '      衡阳华菱钢管有限公司']
df_24 = df_24.reset_index(drop=True)
# 利润总额
profit1 = df_21.loc[0]['CURR_YEAR_VALUE_2']
profit2 = df_22.loc[0]['CURR_YEAR_VALUE_2']
profit3 = df_23.loc[0]['CURR_YEAR_VALUE_2']
profit4 = df_24.loc[0]['CURR_YEAR_VALUE_2']
profit1 = profit1 / 10000
profit2 = profit2 / 10000
profit3 = profit3 / 10000
profit4 = profit4 / 10000

df_11 = df_1[df_1['COMPANY_NAME'] == '湖南钢铁集团有限公司']
df_11 = df_11.reset_index(drop=True)
df_12 = df_1[df_1['COMPANY_NAME'] == '其中：华菱湘潭钢铁有限公司']
df_12 = df_12.reset_index(drop=True)
df_13 = df_1[df_1['COMPANY_NAME'] == '      华菱涟源钢铁有限公司']
df_13 = df_13.reset_index(drop=True)
df_14 = df_1[df_1['COMPANY_NAME'] == '      衡阳华菱钢管有限公司']
df_14 = df_14.reset_index(drop=True)
# 吨钢利润
profit_unit1 = df_11.loc[0]['CURR_YEAR_VALUE_1']
profit_unit2 = df_12.loc[0]['CURR_YEAR_VALUE_1']
profit_unit3 = df_13.loc[0]['CURR_YEAR_VALUE_1']
profit_unit4 = df_14.loc[0]['CURR_YEAR_VALUE_1']
df_31 = df_3[df_3['COMPANY_NAME'] == '湖南钢铁集团有限公司']
df_31 = df_31.reset_index(drop=True)
# 粗钢产量
production1 = df_31.loc[0]['钢产量']
profit_unit11 = profit1 / production1 * 10000
# FMG投资收益
# 猜测前三个月倍数增加，

def check_excel_file_exists(directory, filename):
    full_path = os.path.join(directory, filename)
    if os.path.isfile(full_path) and (filename.endswith('.xls') or filename.endswith('.xlsx')):
        return True
    else:
        return False
directory = 'D:/repos/sicost/湖南钢铁'  # 替换为你的目录路径
filename = month + '.xlsx'  # 替换为你要检查的文件名
if check_excel_file_exists(directory, filename):
    print('已经导入过数据：FMG投资收益、VAMA')
    xlsx_name = 'D:/repos/sicost/湖南钢铁/' + month + '.xlsx'
    df_4 = pd.read_excel(xlsx_name)
    fmg = df_4.iloc[-1]['FMG投资收益']
    vama = df_4.iloc[-1]['VAMA']
    df_out = df_4.copy()
    df_out = df_4.drop(-1)
    dict_out1 = {}
    dict_out1['MONTH'] = month
    dict_out1['粗钢产量'] = production1
    dict_out1['利润总额'] = profit1
    dict_out1['吨钢利润'] = profit_unit11
    dict_out1['FMG投资收益'] = fmg
    dict_out1['VAMA'] = vama
    dict_out1['是否真实'] = 1
    new_row = pd.Series(dict_out1)
    df_out1 = df_out.append(new_row, ignore_index=True)
    # 将其余字段拼接进去重新更新数据库
    writer = pd.ExcelWriter('D:/repos/sicost/湖南钢铁/' + month + '.xlsx')
    df_out1.to_excel(writer, sheet_name='Sheet1', index=False)
    writer.save()
else:
    print('读本年之前月份数据计算FMG投资收益、VAMA')
    xlsx_name = 'D:/repos/sicost/湖南钢铁/' + pre_month + '.xlsx'
    df_4 = pd.read_excel(xlsx_name)
    #猜测是先查看去年两个真实数据的平均增长
    last_year_increase_fmg = 4.5
    last_year_increase_vama = 2
    #查看本年是否有平局增长
    df_41 = df_4[df_4['是否真实'] == 1]
    if len(df_41) >= 2:
        print('ok')
        increase_fmg = (df_4.iloc[-1]['FMG投资收益'] - df_4.iloc[-2]['FMG投资收益']) / (
                int(df_4.iloc[-1]['MONTH']) - int(df_4.iloc[-2]['MONTH']))
        increase_vama = (df_4.iloc[-1]['VAMA'] - df_4.iloc[-2]['VAMA']) / (
                int(df_4.iloc[-1]['MONTH']) - int(df_4.iloc[-2]['MONTH']))
    else:
        increase_fmg = last_year_increase_fmg
        increase_vama = last_year_increase_vama
    if month_int in [2, 3]:
        fmg = df_4.iloc[0]['FMG投资收益'] * month_int
        vama = df_4.iloc[0]['VAMA'] * month_int
    else:
        fmg = df_4.iloc[-1]['FMG投资收益'] + increase_fmg
        vama = df_4.iloc[-1]['VAMA'] + increase_vama
    df_out1 = df_4.copy()
    dict_out1 = {}
    dict_out1['MONTH'] = month
    dict_out1['粗钢产量'] = production1
    dict_out1['利润总额'] = profit1
    dict_out1['吨钢利润'] = profit_unit11
    dict_out1['FMG投资收益'] = fmg
    dict_out1['VAMA'] = vama
    dict_out1['是否真实'] = 0
    new_row = pd.Series(dict_out1)
    df_out1 = df_out1.append(new_row, ignore_index=True)
    # 将其余字段拼接进去重新更新数据库
    writer = pd.ExcelWriter('D:/repos/sicost/湖南钢铁/' + month + '.xlsx')
    df_out1.to_excel(writer, sheet_name='Sheet1', index=False)
    writer.save()
huanyuan_profit = profit1 - fmg - (vama - vama * 0.85 * 0.5)
huanyuan_profit_unit = huanyuan_profit / production1 * 10000
df_0 = df_0.reset_index(drop=False)
df_0.rename(columns={'index': 'INDEX'}, inplace=True)
dict_0 = {}
dict_0['INDEX'] = 5.5
dict_0['COMPANY_NAME'] = '首钢集团（还原矿石利润）'
dict_0['吨钢利润'] = new_profit_unit
new_row = pd.Series(dict_0)
df_0 = df_0.append(new_row, ignore_index=True)
dict_0['INDEX'] = 6.5
dict_0['COMPANY_NAME'] = '湖南钢铁（还原FMG投资收益及VAMA利润）'
dict_0['吨钢利润'] = huanyuan_profit_unit
new_row = pd.Series(dict_0)
df_0 = df_0.append(new_row, ignore_index=True)
df_0 = df_0.sort_values(by=['INDEX'], ascending=[True])
df_0 = df_0.reset_index(drop=True)
df_0.drop(['INDEX'], axis=1, inplace=True)

writer = pd.ExcelWriter('D:/repos/sicost/' + month + '/NEW_普碳行业前十.xlsx')
df_0.to_excel(writer, sheet_name='Sheet1', index=False)
writer.save()
print('finish')